# Copyright (c) 2005,2006,2007 Dynamic Solutions Inc. (support@dynamic-solutions.com)
#
# This file is part of DataZoomer.
#
# DataZoomer is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# DataZoomer is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

"""Provide high level interface to access control mechanism"""


from zoom import zoomer
from time import localtime, strftime
from dzutil import today, now

class Acl:
    audit_table = 'dz_auditlog'
    users_table = 'dz_users'

    def __init__(self,db):
        self.db = db

    def __audit__(self,fn,note1='',note2=''):
        _app = zoomer.app.name
        _action = fn[:2]
        _doneby = zoomer.loginid
        _dt = strftime('%Y-%m-%d %H:%M:%S',localtime())
        _date = _dt[:_dt.find(' ')].strip()
        _time = _dt[_dt.find(' '):].strip()

        cmd = "insert into %s (app,action,subject1,subject2,doneby,date,time) values (%s,%s,%s,%s,%s,%s,%s);" % (
                    self.audit_table,'%s','%s','%s','%s','%s','%s','%s')
        curs = self.db(cmd,_app,_action,note1,note2,_doneby,_date,_time)

    def query(self,cmd):
        return self.db(cmd)

    def exec_SQL(self,cmd,*args):
        if args: return self.db(cmd,*args)
        else: return self.db(cmd)

    def build_member_list(self):
        return self.query('select distinct userid, groupid from dz_members')

    def build_subgroup_list(self):
        return self.query('select distinct subgroupid, groupid from dz_subgroups')

    def build_my_groups(self,loginid):
        return self.query('select distinct b.groupid from dz_users a, dz_members b where a.loginid="'+loginid+'" and a.userid=b.userid')

    def get_ancestors(self,child,allparents,level):
        # Test for loop in database, 20 is unreasonable for most enterprises
        if level > 20:
           return []
        if allparents.has_key(child):
          myparents = allparents[child]
          parents_ancestors = []
          for parent in myparents:
              parents_ancestors += self.get_ancestors(parent,allparents,level+1)
          return myparents + parents_ancestors
        else:
          return []

    def get_user_groups(self,loginid):
        userid = self.get_user_id(loginid)
        mygroups = []
        if loginid=='*':
            cmd = 'SELECT a.name FROM dz_groups a'
        else:
            cmd = 'SELECT a.name FROM dz_groups a, dz_members b WHERE a.groupid=b.groupid and b.userid=%d' % userid
        for rec in self.query(cmd):
            groupid = rec[0]
            mygroups += [groupid]
        return mygroups

    def get_super_groups(self,group):
        groupid = self.get_group_id(group)
        mygroups = []
        for rec in self.query('SELECT a.name FROM dz_groups a, dz_subgroups b WHERE a.groupid=b.groupid and b.subgroupid=%d' % groupid):
            groupid = rec[0]
            mygroups += [groupid]
        return mygroups

    def get_sub_groups(self,group):
        groupid = self.get_group_id(group)
        mygroups = []
        for rec in self.query('SELECT a.name FROM dz_groups a, dz_subgroups b WHERE a.groupid=b.subgroupid and b.groupid=%d' % groupid):
            groupid = rec[0]
            mygroups += [groupid]
        return mygroups

    def get_members(self,group):
        groupid = self.get_group_id(group)
        mymembers = []
        for rec in self.query('SELECT a.loginid FROM dz_users a, dz_members b WHERE a.userid=b.userid and b.groupid=%d' % groupid):
            loginid = rec[0]
            mymembers.append(loginid)
        return mymembers

    def get_groups(self,loginid):
        userid = self.get_user_id(loginid)
        mygroups = []
        for rec in self.query('SELECT groupid FROM dz_members WHERE userid=%d' % userid):
            groupid = rec[0]
            mygroups += [groupid]
        allparents = {}
        for rec in self.query('SELECT subgroupid,groupid FROM dz_subgroups ORDER BY subgroupid'):
            subgroup = rec[0]
            group    = rec[1]
            if allparents.has_key(subgroup):
                allparents[subgroup] += [group]
            else:
                allparents[subgroup] = [group]
        ancestors = []
        for group in mygroups:
            ancestors += self.get_ancestors(group,allparents,0)
        groups = []
        allmygroups = mygroups + ancestors
        for rec in self.query('SELECT groupid,name FROM dz_groups'):
            groupid = rec[0]
            name    = rec[1].strip()
            if groupid in allmygroups:
                groups += [name]
        return groups


    def is_developer(self,loginid):
        return self.is_member(loginid,zoomer.developer_group)

    def is_member(self,loginid,groupname):
        return groupname in self.get_groups(loginid)

    def o_is_member(self,loginid,groupname):
        target = self.get_group_id(groupname)
        mygroups = self.build_my_groups(loginid)
        for row in mygroups:
            if row[0] == target:
                return 1
        membershiplist = self.build_subgroup_list()
        for row in mygroups:
            if self.is_subgroup_of(row[0],target,membershiplist):
                return 1
        return 0

    def is_member_batch(self,loginid,groupname,memberlist,subgrouplist):
        import string
        target = self.get_group_id(groupname)
        for row in memberlist:
            if (row[1] == target) and (row[0] == self.get_user_id(loginid)):
                return 1
        for row in memberlist:
            if (row[0] == self.get_user_id(loginid)):
                if self.is_subgroup_of(row[1],target,subgrouplist):
                    return 1
        return 0


    def is_subgroup_of(self,subgroup,group,subgrouplist):
        if subgroup == group:
            return 1
        else:
            # find supergroups of subgroup
            for row in subgrouplist:
                if subgroup == row[0] and self.is_subgroup_of(row[1],group,subgrouplist):
                    return 1
            return 0


    def lookup(self,target,table,column,key):
        cmd = "SELECT %s FROM %s WHERE %s=%s" % (target,table,column,'%s')
        rec = self.exec_SQL(cmd,key)
        if len(rec)==1:
            return rec[0][0]
        else:
            raise 'Invalid %s: %s' % (column,str(key))

    # Contacts
    def add_contact(self,user,passwd,firstname,lastname,email,phone='',fax='',status='A'):
        self.__audit__('AC',user,'status=%s'%status)
        cmd = "delete from dz_users where email='%s' and status='R'" % (email)
        curs = self.exec_SQL(cmd)
        cmd = "insert into dz_users (loginid,password,firstname,lastname,email,phone,fax,dtadd,dtupd,status) values (%s,OLD_PASSWORD(%s),%s,%s,%s,%s,%s,%s,%s,%s)"
        self.exec_SQL(cmd,user,passwd,firstname,lastname,email,phone,fax,now,now,status)
        lastrowid = self.db.lastrowid
        ## add to default groups
        try: self.attach_member(user,'users')
        except: pass
#        try: self.attach_member(user,'everyone')
#        except: pass
        return lastrowid

    def update_contact(self,userid,loginid,firstname,lastname,email,phone,fax):
        cmd = 'update dz_users set fax=%s, dtupd=now(), firstname=%s, lastname=%s, phone=%s, email=%s, loginid=%s where userid=%s' % (
                    '%s','%s','%s','%s','%s','%s','%s')
        return self.exec_SQL(cmd,fax,firstname,lastname,phone,email,loginid,userid)

    def activate_contact(self,loginid):
        self.__audit__('AV',loginid)
        cmd = "update dz_users set status='A',dtupd=now() where loginid='%s' and status='R'" % loginid
        curs = self.exec_SQL(cmd)
        return 1

    def deactivate_contact(self,loginid):
        self.__audit__('DV',loginid)
        cmd = "update dz_users set status='R',dtupd=now() where loginid=%s and status='A'"
        curs = self.exec_SQL(cmd,loginid)
        return 1

    def encrypt_password(self,password):
        cmd = "select OLD_PASSWORD('%s')" % password
        curs = self.exec_SQL(cmd)
        return curs[0][0]

    def set_password(self,loginid,password,encrypted=0):
        self.__audit__('PW',loginid)
        pwclause = (encrypted and "%s" or "OLD_PASSWORD(%s)")
        cmd = "update %s set password=%s,dtupd=now() where loginid='%s'" % (self.users_table,pwclause,loginid)
        self.exec_SQL(cmd,password)
        return 1

    def get_user_id(self,loginid):
        return self.lookup('userid','dz_users','loginid',loginid)

    def get_login_id(self,userid):
        return self.lookup('loginid','dz_users','userid',userid)

    def is_user(self,loginid):
        try: self.get_user_id(loginid)
        except: return 0
        return 1

    def delete_contact(self,loginid):
        self.__audit__('DC',loginid)
        self.exec_SQL('delete from dz_members where userid=%s',self.get_user_id(loginid))
        self.exec_SQL("delete from dz_users WHERE loginid=%s",loginid)
        return self.db.lastrowid

    # Groups
    def add_group(self,name,type,descr,admin):
        self.__audit__('AG',name,'admin=%s'%admin)
        cmd = "INSERT INTO dz_groups VALUES (null,'%s','%s','%s','%s')" % (type,name,descr,admin)
        curs = self.exec_SQL(cmd)
        lastrowid = self.db.lastrowid
        return lastrowid

    def get_group_id(self,groupname):
        return self.lookup('groupid','dz_groups','name',groupname)

    def get_group_name(self,groupid):
        return self.lookup('name','dz_groups','groupid',groupid)

    def delete_group(self,groupid):
        self.__audit__('DG',self.get_group_name(groupid))
        self.exec_SQL("DELETE FROM dz_subgroups WHERE groupid='%s' or subgroupid='%s'" % (groupid,groupid))
        self.exec_SQL("DELETE FROM dz_members WHERE groupid='%s'" % groupid)
        self.exec_SQL("DELETE FROM dz_groups WHERE groupid='%s'" % groupid)

    # Memberships
    def attach_member(self,contactname,groupname):
        self.__audit__('AM',contactname,groupname)
        userid = self.get_user_id(contactname)
        groupid = self.get_group_id(groupname)
        cmd = "INSERT INTO dz_members VALUES (%s,%s)" % (userid,groupid)
        curs = self.exec_SQL(cmd)

    def detach_member(self,contactname,groupname):
        self.__audit__('DM',contactname,groupname)
        userid = self.get_user_id(contactname)
        groupid = self.get_group_id(groupname)
        cmd = "DELETE FROM dz_members WHERE userid='%s' and groupid='%s'" % (userid,groupid)
        curs = self.exec_SQL(cmd)

    # Subgroups
    def attach_subgroup(self,subgroupname,groupname):
        self.__audit__('AS',subgroupname,groupname)
        subgroupid = self.get_group_id(subgroupname)
        groupid = self.get_group_id(groupname)
        cmd = "INSERT INTO dz_subgroups VALUES (%s,%s)" % (groupid,subgroupid)
        curs = self.exec_SQL(cmd)

    def detach_subgroup(self,subgroupname,groupname):
        self.__audit__('DS',subgroupname,groupname)
        subgroupid = self.get_group_id(subgroupname)
        groupid = self.get_group_id(groupname)
        cmd = "DELETE FROM dz_subgroups WHERE subgroupid='%s' and groupid='%s'" % (subgroupid,groupid)
        curs = self.exec_SQL(cmd)

    # Dump table contents for debugging purposes
    def dump(self,tablename):
        print 'Dump of %s' % tablename
        rs = self.exec_SQL('select * from %s' % tablename)
        for rec in rs: print rec


if __name__ == '__main__':
     import dzdb,MySQLdb
     db = dzdb.Database(MySQLdb.Connect,host='localhost',user='testuser',passwd='password',db='test')
     acl = Acl(db)
     import unittest
     class AclTest(unittest.TestCase):
        db('delete from dz_users where loginid="testuser"')
        db('delete from dz_users where loginid="testuser2"')
        db('delete from dz_users where loginid="testuser3"')
        def test_Groups(self):
            groupid = acl.add_group('UNITTEST1','U','Test User Group','dzadmin')
            self.assertEqual(len(db('select * from dz_groups where name="UNITTEST1"').__dict__['data']),1)
            groupidcheck = acl.get_group_id('UNITTEST1')
            self.assertEqual(groupid,groupidcheck)
            self.assertEqual(acl.get_group_name(groupid),'UNITTEST1')
            acl.delete_group(groupid)
            self.assertEqual(len(db('select * from dz_groups where name="UNITTEST1"').__dict__['data']),0)
        def test_SubGroups(self):
            db('delete from dz_groups where name="UNITTEST3"')
            db('delete from dz_groups where name="UNITTEST2"')
            group1 = acl.add_group('UNITTEST3','U','Test User Group','dzadmin')
            group2 = acl.add_group('UNITTEST2','U','Test User Group2','dzadmin')
            acl.attach_subgroup('UNITTEST2','UNITTEST3')
            self.assertEqual(len(db('select * from dz_subgroups where groupid='+str(group1)+' and subgroupid='+str(group2)).__dict__['data']),1)
            acl.detach_subgroup('UNITTEST2','UNITTEST3')
            self.assertEqual(len(db('select * from dz_subgroups where groupid='+str(group1)+' and subgroupid='+str(group2)).__dict__['data']),0)
            acl.delete_group(group1)
            acl.delete_group(group2)
        def test_Contacts(self):
            id = acl.add_contact('testuser','pass','User','Name','someone@somewhere.ca','R')
            self.assertEqual(len(db('select * from dz_users where loginid="testuser"')),1)
            acl.activate_contact("testuser")
            self.assertEqual(len(db('select * from dz_users where loginid="testuser" and status="A"')),1)
            passE = acl.encrypt_password('pass')
            self.assert_(passE!='pass')
            acl.set_password('testuser','passnew',encrypted=1)
            self.assertEqual(len(db('select * from dz_users where loginid="testuser" and password="passnew"')),1)
            acl.set_password('testuser','passnew',encrypted=0)
            self.assertEqual(len(db('select * from dz_users where loginid="testuser" and password!="passnew"')),1)
            self.assertEqual(acl.get_user_id('testuser'),id)
            self.assertEqual(acl.get_login_id(id),'testuser')
            acl.delete_contact('testuser')
            self.assertEqual(len(db('select * from dz_users where loginid="testuser"')),0)
        def test_MemberShips(self):
            db('delete from dz_groups where name="UNITTEST4"')
            groupid = acl.add_group('UNITTEST4','U','Test User Group','dzadmin')
            id = acl.add_contact('testuser2','pass','User','Name','someone@somewhere.ca')
            acl.attach_member('testuser2','UNITTEST4')
            self.assertEqual(len(db('select * from dz_members where userid='+str(id)+' and groupid='+str(groupid)).__dict__['data']),1)
            acl.detach_member('testuser2','UNITTEST4')
            self.assertEqual(len(db('select * from dz_members where userid='+str(id)+' and groupid='+str(groupid)).__dict__['data']),0)
            acl.delete_group(groupid)
            acl.delete_contact('testuser2')
        def test_Gets(self):
            db('delete from dz_groups where name="UNITTEST5" or name="UNITTEST6" or name="UNITTEST7"')
            db('delete from dz_users where loginid="testuser3"')
            db("delete from dz_users where loginid='testuser4'")
            groupid = acl.add_group('UNITTEST5','U','Test User Group','dzadmin')
            groupid2 = acl.add_group('UNITTEST6','U','Test User Group','dzadmin')
            groupid3 = acl.add_group('UNITTEST7','U','Test User Group','dzadmin')
            id = acl.add_contact('testuser3','pass','User','Name','someone@somewhere.ca')
            id = acl.add_contact('testuser4','pass','Test','User','test@organization.org')
            acl.attach_member('testuser3','UNITTEST5')
            acl.attach_member('testuser4','UNITTEST5')
            acl.attach_member('testuser3','UNITTEST6')
            acl.attach_subgroup('UNITTEST7','UNITTEST5')
            acl.attach_subgroup('UNITTEST7','UNITTEST6')

            user = acl.get_user_groups('testuser3')
            self.assert_('UNITTEST5' in user and 'UNITTEST6' in user and len(user)==2)

            super = acl.get_super_groups('UNITTEST7')
            self.assert_('UNITTEST5' in super and 'UNITTEST6' in super and len(super)==2)

            groups = acl.get_groups('testuser3')
            self.assert_('UNITTEST5' in groups and 'UNITTEST6' in groups and len(groups)==2)

            members = acl.get_members('UNITTEST5')
            self.assert_('testuser3' in members and 'testuser4' in members and len(groups)==2)

            acl.detach_subgroup('UNITTEST7','UNITTEST5')
            acl.detach_subgroup('UNITTEST7','UNITTEST6')
            acl.detach_member('testuser3','UNITTEST5')
            acl.detach_member('testuser4','UNITTEST5')
            acl.detach_member('testuser3','UNITTEST6')
            acl.delete_group(groupid)
            acl.delete_group(groupid2)
            acl.delete_group(groupid3)
            acl.delete_contact('testuser3')
            acl.delete_contact('testuser4')
        def test_Is(self):
            db('delete from dz_groups where name="UNITTEST5" or name="UNITTEST6" or name="UNITTEST7"')
            db('delete from dz_users where userid="testuser3"')
            groupid = acl.add_group('UNITTEST5','U','Test User Group','dzadmin')
            groupid2 = acl.add_group('UNITTEST6','U','Test User Group','dzadmin')
            groupid3 = acl.add_group('UNITTEST7','U','Test User Group','dzadmin')
            db('delete from dz_users where loginid="testuser3"')
            id = acl.add_contact('testuser3','pass','User','Name','someone@somewhere.ca')
            acl.attach_member('testuser3','UNITTEST5')
            acl.attach_member('testuser3','UNITTEST6')
            acl.attach_subgroup('UNITTEST7','UNITTEST5')
            acl.attach_subgroup('UNITTEST7','UNITTEST6')
            self.assertEqual(acl.is_member('testuser3','UNITTEST5'),1)
            self.assertEqual(acl.is_member('testuser3','UNITTEST7'),0)
            self.assertEqual(acl.o_is_member('testuser3','UNITTEST5'),1)
            self.assertEqual(acl.o_is_member('testuser3','UNITTEST7'),0)
            memberlist = acl.build_member_list()
            sglist = acl.build_subgroup_list()
            self.assertEqual(acl.is_member_batch('testuser3','UNITTEST7',memberlist,sglist),0)
            self.assertEqual(acl.is_subgroup_of(acl.get_group_id('UNITTEST7'),acl.get_group_id('UNITTEST5'),sglist),1)
            self.assertEqual(acl.is_subgroup_of(acl.get_group_id('UNITTEST6'),acl.get_group_id('UNITTEST5'),sglist),0)
            acl.detach_subgroup('UNITTEST7','UNITTEST5')
            acl.detach_subgroup('UNITTEST7','UNITTEST6')
            acl.detach_member('testuser3','UNITTEST5')
            acl.detach_member('testuser3','UNITTEST6')
            acl.delete_group(groupid)
            acl.delete_group(groupid2)
            acl.delete_group(groupid3)
            acl.delete_contact('testuser3')
     unittest.main()
